package com.pomr.service.equipmentstorage.impl;

import java.text.SimpleDateFormat;
import java.util.*;

import com.pomr.entity.StorageAllocation;
import com.pomr.model.equipmentstorage.*;
import com.pomr.model.menu.EquipmentKind;
import com.pomr.model.sys.FaultTreatment;
import com.pomr.model.sys.MsgContent;
import com.pomr.util.common.ResponseData;
import com.pomr.util.page.PageParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.pomr.dao.equipmentstorage.StorageDao;
import com.pomr.model.simstorage.SimCount;
import com.pomr.model.simstorage.SimStorage;
import com.pomr.model.simstorage.SimStorageDetail;
import com.pomr.model.sys.Agency;
import com.pomr.model.sys.User;
import com.pomr.service.equipmentstorage.StorageService;
import com.pomr.util.page.Page;
@Service
public class StorageServiceImpl implements StorageService {
	@Autowired
	@Qualifier("storageDaoImpl")
	private StorageDao storageDao;

	@Override
	public Page<List<EquipmentStorage>> searchStorages(String equipmentid, String agencyid, String manufacturerid,
			String page, String limit) throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("from EquipmentStorage ");
		int i=0;
		if(equipmentid!=null && !equipmentid.equals("")){
			i++;
			sb.append("where equipment.id = "+equipmentid+" ");
		}
		if(agencyid!=null && !agencyid.equals("")){
			
			if(i>0){
				sb.append("and ");
			}else{
				sb.append("where ");
			}
			sb.append("agency.id = "+agencyid+" ");
			i++;
		}
		if(manufacturerid!=null && !manufacturerid.equals("")){
			
			if(i>0){
				sb.append("and ");
			}else{
				sb.append("where ");
			}
			sb.append("manufacturer.id = "+manufacturerid+" ");
			i++;
		}
		
		
		String hqlStr = sb.toString();
		Page<List<EquipmentStorage>> p = new Page<List<EquipmentStorage>>();
		p.setCunrrentPage(Integer.valueOf(page));
		p.setPageSize(Integer.valueOf(limit));
		
		return storageDao.showPage(hqlStr, "select count(1) "+hqlStr, p);
	}

	@Override
	public Page<List<EquipmentStorageDetail>> searchStorageDetails(String id, String page, String limit) throws Exception {
		if(id==null || id.equals("")){
			return null;
		}
		//查看库存--未被领用的设备以及未被执行清库操作的
		StringBuilder sb = new StringBuilder();
		sb.append("from EquipmentStorageDetail detail where equipmentStorage.id = "+id);
		sb.append(" and (detail.equipmentStatus.id!=2 and detail.equipmentCon.id!=3)");
		sb.append(" and detail.id not in (select match.equipmentdetail.id from Receive where receiveTime = null)");
		String hqlStr = sb.toString();
		List<EquipmentStorageDetail> details = storageDao.findByHql(hqlStr);
		Page<List<EquipmentStorageDetail>> p = new Page<List<EquipmentStorageDetail>>();
		p.setCunrrentPage(Integer.valueOf(page));
		p.setPageSize(Integer.valueOf(limit));
		p.setRows(details);
		return storageDao.showPage(hqlStr, "select count(1) "+hqlStr, p);
	}

	@Override
	@Transactional(rollbackFor=Exception.class)
	public boolean deleteStorageDetails(String[] ids) throws Exception {
		int len = ids.length;
		EquipmentStorageDetail detail = null;
		for(int i=0; i<len; i++){
			//先查询
			List<EquipmentStorageDetail> ss = storageDao.findByHql("from EquipmentStorageDetail left join fetch equipmentStorage where id = "+ids[i]+" and equipmentStatus.id != 2");
			if(ss==null || ss.size()<=0){//设备已被使用
				throw new Exception("设备正在被使用");
			}
			detail = ss.get(0);
			if(!storageDao.remove("delete from equipment_storage_detail where id = "+ids[i])){
				throw new Exception("删除失败");
			}
		}
		//删除成功 更新库存
		String hql = "update EquipmentStorage set stockNumber = stockNumber-"+len+" where equipment.id="+detail.getEquipmentStorage().getId();
		if(!storageDao.editHql(hql)){
			throw new Exception("更新库存失败");
		}
		return true;
	}

	@Override
	@Transactional(rollbackFor=Exception.class)
	public boolean editMoney(EquipmentStorage storage) throws Exception {
		String hql = "update EquipmentStorage set earnestmoney = "+storage.getEarnestmoney()+" where id = "+storage.getId();
		return storageDao.editHql(hql);
	}
	
	
	@Override
	public boolean damageStorageDetails(String[] ids) throws Exception {
		int len = ids.length;
		for(int i=0; i<len; i++){
			String hql = "update EquipmentStorageDetail set equipmentCon.id=2 where id = "+ids[i]+"";
			if(!storageDao.editHql(hql)){
				throw new Exception("设备报损失败");
			}
		}
		return true;
	}

	@Override
	public boolean repairStorageDetails(String[] ids) throws Exception {
		int len = ids.length;
		for(int i=0; i<len; i++){
			String hql = "update EquipmentStorageDetail set equipmentCon.id=1 where id = "+ids[i]+"";
			if(!storageDao.editHql(hql)){
				throw new Exception("设备修复失败");
			}
		}
		return true;
	}

	@Override
	public boolean cleanStorageDetails(String[] ids, String reason) throws Exception {
		int len = ids.length;
		for(int i=0; i<len; i++){
			String hql = "from EquipmentStorageDetail where id="+ids[i]+" and equipmentStatus.id!=2 and equipmentStatus.id!=3";
			List<EquipmentStorageDetail> details = storageDao.findByHql(hql);
			if(details!=null && details.size()>0){
				hql = "update EquipmentStorageDetail set equipmentCon.id=3,reason='"+reason+"' where id = "+ids[i]+"";
				if(!storageDao.editHql(hql)){
					throw new Exception("设备清库失败");
				}
			}else{
				throw new Exception("设备清库失败,设备已经绑定");
			}
			
		}
		//减库存
		String hql = "update EquipmentStorage set stockNumber = stockNumber-"+len+" where id=(select equipmentStorage.id from EquipmentStorageDetail where id="+ids[0]+")";
		if(!storageDao.editHql(hql)){
			throw new Exception("设备清库失败,更新库存失败");
		}
		return true;
	}

	/**
	 * 设备库存调度
	 * 检查设备库存
	 * 确认目标库存记录是否存在？不存在则新增
	 * 更新设备信息 所属机构变更
	 * 更新库存记录-1.目标库存更新 2.现有库存更新
	 * 检查设备是否存在绑定SIM卡？存在一起进行调度操作
	 * 。。。。。。
	 */
	@Override
	@Transactional(rollbackFor=Exception.class)
	public int allocationStorage(String id, String sn1, String sn2, String agid,User user) throws Exception {
		String sn = "";
		if(sn1==null || "".equals(sn1)){
			sn = sn2;
		}else if(sn2==null || "".equals(sn2)){
			sn = sn1;
		}
		//查询设备库存
		String hql = "from EquipmentStorage where id = "+id;
		List<EquipmentStorage> oldstor = storageDao.findByHql(hql);
		if(oldstor==null || oldstor.isEmpty()){
			throw new Exception("设备库存未找到");
		}
		EquipmentStorage old = oldstor.get(0);
		hql = "from EquipmentStorage where equipment.id ="+old.getEquipment().getId()+" and manufacturer.id="+old.getManufacturer().getId()+" and agency.id="+agid;
		List<EquipmentStorage> newstor = storageDao.findByHql(hql);
		int newid = 0;
		if(newstor==null || newstor.isEmpty()){//新库存不存在 添加
			EquipmentStorage str = new EquipmentStorage();
			str.setEquipment(old.getEquipment());
			str.setManufacturer(old.getManufacturer());
			Agency a = new Agency();
			a.setId(new Integer(agid));
			str.setAgency(a);
			str.setStockNumber(0);
			newid = storageDao.addNumKey(str);
			System.out.println("newid:"+newid);
			if(newid <=0){
				throw new Exception("添加新库存失败");
			}
		}else{
			newid = newstor.get(0).getId();
		}
		System.out.println("newid:"+newid);

		//添加设备库存调拨记录
		AllocationMessage allocationMessage = new AllocationMessage();
		allocationMessage.setCreateTime(new Date());
		allocationMessage.setCreateUserId(user.getId());
		allocationMessage.setEquipmentId(old.getEquipment().getId());
		allocationMessage.setEquipmentName(old.getEquipmentName());
		allocationMessage.setEspecification(old.getEspecification());
		allocationMessage.setManufacturerId(old.getManufacturer().getId());
		allocationMessage.setManufacturerName(old.getManufacturerName());
		allocationMessage.setOldAgencyId(old.getAgency().getId());
		allocationMessage.setNewAgencyId(Integer.valueOf(agid));
		String allocationId = null;
		if((allocationId = storageDao.addStringKey(allocationMessage)) == null){
			throw new Exception("添加库存调拨记录失败");
		}
		//获取调拨设备详情
		String detailhql = null;
		if(sn.equals("")) {
			detailhql = "from EquipmentStorageDetail where equipmentStorage.id=" + id + " and sN>='" + sn1 + "' and sN<='" + sn2 + "' and "
					+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null)";
		}else{
			detailhql = "from EquipmentStorageDetail where equipmentStorage.id="+id +" and sN='"+sn+"' and "
					+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null)";
		}
		List<EquipmentStorageDetail> details =  storageDao.findByHql(detailhql);
		if(details != null && !details.isEmpty()){
			for(EquipmentStorageDetail detail:details){
				AllocationMessageDetail allocationMessageDetail = new AllocationMessageDetail();
				allocationMessageDetail.setAllocationId(allocationId);
				allocationMessageDetail.setSn(detail.getsN());
				if(!storageDao.add(allocationMessageDetail)){
					throw new Exception("添加库存调拨记录详情失败");
				}
			}
			allocationMessage.setId(allocationId);
			allocationMessage.setNum(details.size());
			if(!storageDao.editEntity(allocationMessage)){
				throw new Exception("添加库存调拨记录详情失败-更新设备调拨数目");
			}
		}

		//更新设备详细信息
		String simhql = null;
		if(sn.equals("")){
			//更新库存的设备归属 
			hql = "update EquipmentStorageDetail set equipmentStorage.id="+newid+" where equipmentStorage.id="+id +" and sN>='"+sn1+"' and sN<='"+sn2+"' and "
							+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null)";
			simhql = "from DeviceMatch d where d.equipmentdetail.id in (select id from EquipmentStorageDetail where equipmentStorage.id="+id +" and sN>='"+sn1+"' and sN<='"+sn2+"' and "
							+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null))";
			
		}else{
			hql = "update EquipmentStorageDetail set equipmentStorage.id="+newid+" where equipmentStorage.id="+id +" and sN='"+sn+"' and "
							+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null)";
			simhql = "from DeviceMatch d where d.equipmentdetail.id in(select id from EquipmentStorageDetail where equipmentStorage.id="+id +" and sN='"+sn+"' and "
							+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null))";
		}
		
		//查询出已绑定sim卡的库存
		List<SimStorageDetail> sdetails =  storageDao.findByHql("select d.simdetail "+simhql);

		//添加sim卡库存调拨记录
		System.out.println("更新sim卡数目："+sdetails.size());
		List<SimCount> scount = storageDao.findByHql("select new com.pomr.model.simstorage.SimCount(d.simdetail.simStorage as storage,count(d.simdetail.simStorage) as scount) "+simhql+" group by d.simdetail.simStorage");
		int upnum = storageDao.editNumHql(hql);
		if(upnum<=0){
			throw new Exception("更新设备信息失败");
		}
		//更新库存
		hql = "update EquipmentStorage set stockNumber=stockNumber+"+upnum+" where id="+newid;
		if(!storageDao.editHql(hql)){
			throw new Exception("更新库存信息失败");
		}
		//更新原库存
		hql = "update EquipmentStorage set stockNumber=stockNumber-"+upnum+" where id="+id;
		if(!storageDao.editHql(hql)){
			throw new Exception("更新现有库存信息失败");
		}
		//------------设备库存调度完成
		//如果有设备已经绑定了sim卡 sim卡一并进行移库调度
		if(sdetails!=null && sdetails.size()>0){//存在已经绑定的sim卡
			//更新SIM卡库存
			String schql = "";
			for(SimCount sc:scount){
				schql = "update SimStorage set stockNumber=stockNumber-"+sc.getScount()+" where id="+sc.getStorage().getId();
				if(!storageDao.editHql(schql)){
					throw new Exception("绑定SIM卡调度失败");
				}
				//检查库存记录是否存在
				String scc = "from SimStorage where networkOperator.id ="+sc.getStorage().getNetworkOperator().getId()+" "
						+ "and manufacturer.id="+sc.getStorage().getManufacturer().getId()+" and agency.id="+agid;
				List<SimStorage> sclist = storageDao.findByHql(scc);
				int newsid = 0;
				if(sclist==null || sclist.isEmpty()){//新库存记录不存在 新增记录
					SimStorage s = new SimStorage();
					s.setNetworkOperator(sc.getStorage().getNetworkOperator());
					s.setManufacturer(sc.getStorage().getManufacturer());
					Agency a = new Agency();
					a.setId(new Integer(agid));
					s.setAgency(a);
					s.setEarnestmoney(sc.getStorage().getEarnestmoney());
					s.setStockNumber(sc.getScount());
					newsid = storageDao.addNumKey(s);
					if(newsid <=0){
						throw new Exception("添加绑定的SIM卡新库存失败");
					}
				}else{
					newsid = sclist.get(0).getId();
					//更新新库存
					schql = "update SimStorage set stockNumber=stockNumber+"+sc.getScount()+" where id="+newsid;
					if(!storageDao.editHql(schql)){
						throw new Exception("绑定SIM卡调度失败");
					}
				}

			}
			//更新sim卡
			String idstr = "";
			for(SimStorageDetail sd:sdetails){
				idstr += ","+sd.getId();
			}
			idstr=idstr.replaceFirst(",", "");
			String usimhql = "update SimStorageDetail sd set sd.simStorage.id = "
					+ "(select s.id from SimStorage s where s.networkOperator.id=sd.simStorage.networkOperator.id and s.manufacturer.id=sd.simStorage.manufacturer.id and "
					+ "s.agency.id="+agid+") "
					+ "where sd.id in ("+idstr+")";
			if(!storageDao.editHql(usimhql)){
				throw new Exception("绑定SIM卡调度失败");
			}
			
			
		}
		return upnum;
	}

	@Override
	public Page<List<EquipmentStorageDetail>> searchEquipmentsBySn(String page, String limit, String sn, User user)
			throws Exception {
		String hql = "from EquipmentStorageDetail where equipmentStorage.agency.id=(select agency.id from User where id="+user.getId()+") "
				+ "and (equipmentStatus.id!=2 and equipmentCon.id!=3 and equipmentCon.id!=2) and sn like '%"+sn+"%'";
		Page<List<EquipmentStorageDetail>> p = new Page<List<EquipmentStorageDetail>>();
		p.setCunrrentPage(Integer.valueOf(page==null?"1":page));
		p.setPageSize(Integer.valueOf(limit));
		return storageDao.showPage(hql, "select count(1) "+hql, p);
	}

	@Override
	@Transactional(rollbackFor = Exception.class)
	public ResponseData repairEquip(String sn, String reason) throws Exception {
		//获取sn编码对应的领用记录
		String hql = "from Receive where esN='"+sn+"' and receiveTime=null order by createTime desc";
		List<Receive> receives = storageDao.findByHql(hql);
		if(receives==null || receives.isEmpty()){
			return ResponseData.custom("设备领用记录未找到，是否已经退还设备");
		}
		Receive receive =receives.get(0);
		receive.setStatus(1);
		if(!storageDao.saveOrUpdateEntity(receive)){
			return ResponseData.custom("设备报修失败");
		}
		FaultTreatment faultTreatment = new FaultTreatment();
		faultTreatment.setCreateTime(new Date());
		faultTreatment.setLeaderId(receive.getId());
		faultTreatment.setSn(sn);
		faultTreatment.setFaultType(3);//未知故障错误
		faultTreatment.setFaultmsg(reason);
		faultTreatment.setUpdateTime(new Date());
		faultTreatment.setCreateUserId(1);
		faultTreatment.setUpdateUserId(1);
		if(!storageDao.add(faultTreatment)){
			throw new Exception("添加故障记录失败");
		}
		try {
			//添加设备报修消息
			MsgContent msgContent = new MsgContent();
			msgContent.setType(1);
			User u = new User();
			u.setId(1);
			msgContent.setUser(u);
			msgContent.setTitle("设备报修");
			msgContent.setMsg("设备SN编号：" + sn + " 领用人：" + receive.getRecordName() + " 报修原因：" + reason);
			msgContent.setAgency(receive.getCreateUser().getAgency());
			msgContent.setCreateTime(new Date());
			storageDao.add(msgContent);
		}catch (Exception e){
			e.printStackTrace();
		}
		return ResponseData.ok();
	}

	@Override
	public List<EquipmentKind> searchEquipmentByAgency(String agencyid) throws Exception {
		String hql = "select distinct e.equipment.kind from EquipmentStorage e where e.agency.id="+agencyid+" and e.stockNumber>0";
		return storageDao.findByHql(hql);
	}

	@Override
	public ResponseData seachDetailsBySn(String storageId, String sn1, String sn2) throws Exception {
		if(sn1 == null || sn1.equals("")){
			return ResponseData.custom("设备编号不能为空");
		}
		if(storageId == null || storageId.equals("")){
			return ResponseData.custom("设备库存编号不能为空");
		}
		String hql = "from EquipmentStorageDetail where equipmentStorage.id="+storageId+" ";
		if(sn2!=null && !"".equals(sn2)){
			hql += " and sN>='"+sn1+"'and sN<='"+sn2+"' ";
		}else{
			hql += " and sN='"+sn1+"'";
		}
		List<EquipmentStorageDetail> equipmentStorageDetails = storageDao.findByHql(hql);
		return ResponseData.ok().putDataValue("equipmentStorageDetails",equipmentStorageDetails);
	}

	@Override
	@Transactional(rollbackFor = Exception.class)
	public ResponseData storageAllocation(StorageAllocation storageAllocation, User user) throws Exception {
		if(storageAllocation.getStorageId()==null || "".equals(storageAllocation.getStorageId())){
			return ResponseData.custom("原库存编号不能为空");
		}
		if(storageAllocation.getNewAgencyId()==null || "".equals(storageAllocation.getNewAgencyId())){
			return ResponseData.custom("转入机构编号不能为空");
		}
		if(storageAllocation.getDetails()==null || storageAllocation.getDetails().isEmpty()){
			return ResponseData.custom("没有传入要转库的设备");
		}

		//查询设备库存
		String hql = "from EquipmentStorage where id = "+storageAllocation.getStorageId();
		List<EquipmentStorage> oldstor = storageDao.findByHql(hql);
		if(oldstor==null || oldstor.isEmpty()){
			throw new Exception("设备库存未找到");
		}
		EquipmentStorage old = oldstor.get(0);
		if(old.getAgency().getId() == storageAllocation.getNewAgencyId()){
			return ResponseData.custom("入库机构与库存机构一致，无法转库");
		}
		hql = "from EquipmentStorage where equipment.id ="+old.getEquipment().getId()+" and manufacturer.id="+old.getManufacturer().getId()+" and agency.id="+storageAllocation.getNewAgencyId();
		List<EquipmentStorage> newstor = storageDao.findByHql(hql);
		int newid = 0;
		if(newstor==null || newstor.isEmpty()){//新库存不存在 添加
			EquipmentStorage str = new EquipmentStorage();
			str.setEquipment(old.getEquipment());
			str.setManufacturer(old.getManufacturer());
			Agency a = new Agency();
			a.setId(storageAllocation.getNewAgencyId());
			str.setAgency(a);
			str.setStockNumber(0);
			newid = storageDao.addNumKey(str);
			System.out.println("newid:"+newid);
			if(newid <=0){
				throw new Exception("添加新库存失败");
			}
		}else{
			newid = newstor.get(0).getId();
		}
		System.out.println("newid:"+newid);

		//添加设备库存调拨记录
		AllocationMessage allocationMessage = new AllocationMessage();
		allocationMessage.setCreateTime(new Date());
		allocationMessage.setCreateUserId(user.getId());
		allocationMessage.setEquipmentId(old.getEquipment().getId());
		allocationMessage.setEquipmentName(old.getEquipmentName());
		allocationMessage.setEspecification(old.getEspecification());
		allocationMessage.setManufacturerId(old.getManufacturer().getId());
		allocationMessage.setManufacturerName(old.getManufacturerName());
		allocationMessage.setOldAgencyId(old.getAgency().getId());
		allocationMessage.setNewAgencyId(storageAllocation.getNewAgencyId());
		String allocationId = null;
		if((allocationId = storageDao.addStringKey(allocationMessage)) == null){
			throw new Exception("添加库存调拨记录失败");
		}
		//获取调拨设备详情
		List<EquipmentStorageDetail> details =  storageAllocation.getDetails();
		if(details != null && !details.isEmpty()){
			for(EquipmentStorageDetail detail:details){
				AllocationMessageDetail allocationMessageDetail = new AllocationMessageDetail();
				allocationMessageDetail.setAllocationId(allocationId);
				allocationMessageDetail.setSn(detail.getsN());
				if(detail.getsN()==null || "".equals(detail.getsN())){
					throw new Exception("添加库存调拨记录详情失败,设备SN编码未上传");
				}
				if(!storageDao.add(allocationMessageDetail)){
					throw new Exception("添加库存调拨记录详情失败");
				}
			}
			allocationMessage.setId(allocationId);
			allocationMessage.setNum(details.size());
			if(!storageDao.editEntity(allocationMessage)){
				throw new Exception("添加库存调拨记录详情失败-更新设备调拨数目");
			}
		}
		String ids = "";
		for(EquipmentStorageDetail detail:details){
			ids += ","+detail.getId();
		}
		ids = ids.replaceFirst(",","");
		//更新设备详细信息
		hql = "update EquipmentStorageDetail set equipmentStorage.id="+newid+" where equipmentStorage.id="+storageAllocation.getStorageId() +" and id in ("+ids+") and "
				+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null)";
		String simhql = "from DeviceMatch d where d.equipmentdetail.id in (select id from EquipmentStorageDetail where equipmentStorage.id="+storageAllocation.getStorageId() +" and id in ("+ids+") and "
				+ "id not in (select match.equipmentdetail.id from Receive where receiveTime = null))";

		//查询出已绑定sim卡的库存
		List<SimStorageDetail> sdetails =  storageDao.findByHql("select d.simdetail "+simhql);

		//添加sim卡库存调拨记录
		System.out.println("更新sim卡数目："+sdetails.size());
		List<SimCount> scount = storageDao.findByHql("select new com.pomr.model.simstorage.SimCount(d.simdetail.simStorage as storage,count(d.simdetail.simStorage) as scount) "+simhql+" group by d.simdetail.simStorage");
		int upnum = storageDao.editNumHql(hql);
		if(upnum<=0){
			throw new Exception("更新设备信息失败");
		}
		//更新库存
		hql = "update EquipmentStorage set stockNumber=stockNumber+"+upnum+" where id="+newid;
		if(!storageDao.editHql(hql)){
			throw new Exception("更新库存信息失败");
		}
		//更新原库存
		hql = "update EquipmentStorage set stockNumber=stockNumber-"+upnum+" where id="+storageAllocation.getStorageId();
		if(!storageDao.editHql(hql)){
			throw new Exception("更新现有库存信息失败");
		}
		//------------设备库存调度完成
		//如果有设备已经绑定了sim卡 sim卡一并进行移库调度
		if(sdetails!=null && sdetails.size()>0){//存在已经绑定的sim卡
			//更新SIM卡库存
			String schql = "";
			for(SimCount sc:scount){
				schql = "update SimStorage set stockNumber=stockNumber-"+sc.getScount()+" where id="+sc.getStorage().getId();
				if(!storageDao.editHql(schql)){
					throw new Exception("绑定SIM卡调度失败");
				}
				//检查库存记录是否存在
				String scc = "from SimStorage where networkOperator.id ="+sc.getStorage().getNetworkOperator().getId()+" "
						+ "and manufacturer.id="+sc.getStorage().getManufacturer().getId()+" and agency.id="+storageAllocation.getNewAgencyId();
				List<SimStorage> sclist = storageDao.findByHql(scc);
				int newsid = 0;
				if(sclist==null || sclist.isEmpty()){//新库存记录不存在 新增记录
					SimStorage s = new SimStorage();
					s.setNetworkOperator(sc.getStorage().getNetworkOperator());
					s.setManufacturer(sc.getStorage().getManufacturer());
					Agency a = new Agency();
					a.setId(storageAllocation.getNewAgencyId());
					s.setAgency(a);
					s.setEarnestmoney(sc.getStorage().getEarnestmoney());
					s.setStockNumber(sc.getScount());
					newsid = storageDao.addNumKey(s);
					if(newsid <=0){
						throw new Exception("添加绑定的SIM卡新库存失败");
					}
				}else{
					newsid = sclist.get(0).getId();
					//更新新库存
					schql = "update SimStorage set stockNumber=stockNumber+"+sc.getScount()+" where id="+newsid;
					if(!storageDao.editHql(schql)){
						throw new Exception("绑定SIM卡调度失败");
					}
				}

			}
			//更新sim卡
			String idstr = "";
			for(SimStorageDetail sd:sdetails){
				idstr += ","+sd.getId();
			}
			idstr=idstr.replaceFirst(",", "");
			String usimhql = "update SimStorageDetail sd set sd.simStorage.id = "
					+ "(select s.id from SimStorage s where s.networkOperator.id=sd.simStorage.networkOperator.id and s.manufacturer.id=sd.simStorage.manufacturer.id and "
					+ "s.agency.id="+storageAllocation.getNewAgencyId()+") "
					+ "where sd.id in ("+idstr+")";
			if(!storageDao.editHql(usimhql)){
				throw new Exception("绑定SIM卡调度失败");
			}


		}
		return ResponseData.ok().putDataValue("num", upnum);
	}

	@Override
	public Page<List<AllocationMessage>> findAllocationMessages(PageParam param, User user) throws Exception {
		Map<String, String> option = param.getParams();
		String page = param.getPage();
		String limit = param.getLimit();
		user = storageDao.getById(User.class, user.getId());
		String hql = "from AllocationMessage where oldAgencyId="+user.getAgency().getId();
		Map<String, Object> map = new HashMap<>();
		if(option!=null && !option.isEmpty()){
			String newagency = option.get("newagency");//转入机构
			if(newagency!=null && !newagency.equals("")){
				hql += " and newAgencyId="+newagency;
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String starttime = option.get("starttime");//转入机构
			if(starttime!=null && !starttime.equals("")){
				hql += " and createTime>=:starttime";
				map.put("starttime",sdf.parse(starttime+" 00:00:00"));
			}
			String endtime = option.get("endtime");//转入机构
			if(endtime!=null && !endtime.equals("")){
				hql += " and createTime<=:endtime";
				map.put("endtime",sdf.parse(endtime+" 23:59:59"));
			}
		}
		Page<List<AllocationMessage>> p = new Page<>();
		p.setCunrrentPage(Integer.valueOf(page));
		p.setPageSize(Integer.valueOf(limit));
		return storageDao.showPageParam(hql, "select count(1) "+hql, p, map);
	}

	@Override
	public ResponseData seachAllocationMessageDetails(String aid) throws Exception {
		if(aid==null || aid.equals("")){
			return ResponseData.custom("转库记录主键不能为空");
		}
		String hql = "from AllocationMessageDetail where allocationId='"+aid+"'";
		List<AllocationMessageDetail> allocationMessageDetails = storageDao.findByHql(hql);
		List<String> sns = new ArrayList<>();
		for(AllocationMessageDetail detail:allocationMessageDetails){
			sns.add(detail.getSn());
		}
		return ResponseData.ok().putDataValue("sns",sns);
	}
}
